import os
import hashlib

import jinja2
from flask import Flask, request, jsonify, session, render_template, redirect
from functools import wraps
import mysql.connector
from datetime import datetime

from werkzeug.utils import secure_filename, send_from_directory

app = Flask(__name__)

db_config = {
    "host": "127.0.0.1",
    "user": "root",
    "password": "123456",
    "database": "class",
}

app.secret_key = "your_secret_key_here"

conn = mysql.connector.connect(**db_config)
cursor = conn.cursor()


def hash_password(password: str) -> str:
    """
    使用SHA-256对密码进行简单的哈希处理。
    """
    # 实际应用中应该加盐，这里为了简化示例省略了
    hash_obj = hashlib.sha256()
    hash_obj.update(password.encode('utf-8'))
    return hash_obj.hexdigest()

def route_ping(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        conn.ping(reconnect=True)
        return f(*args, **kwargs)

    return decorated_function


@app.route("/", methods=["GET"])
def index():
    if "user_id" in session:
        return render_template("index.html", username=session["user_id"])
    else:
        return redirect("/login")


@app.route("/<routename>", methods=["GET"])
def index_page(routename):
    return render_template(f"{routename}.html")


# 用户管理接口
@app.route("/api/users", methods=["POST"])
@route_ping
def api_users():
    if request.method == "POST":
        data = request.get_json()

        username = data["username"]
        password = data["password"]
        user_type = data["type"]
        classname = data["classname"]
        pwd_encrypted = hash_password(password)

        # 检查用户名是否已经存在
        cursor.execute("SELECT * FROM users WHERE username = %s", (username,))
        existing_user = cursor.fetchone()
        if existing_user:
            return jsonify({"message": "Username already exists"}), 400

        cursor.execute(
            "SELECT id FROM class WHERE class_name = %s", (classname,)
        )
        res = cursor.fetchone()
        if res is None:
            raise ValueError("No class found with name: " + classname)
        class_id = res[0]

        # 插入新用户
        cursor.execute(
            "INSERT INTO users (username, password, type, class_id) VALUES (%s, %s, %s,%s)",
            (username, pwd_encrypted, user_type, class_id),
        )
        conn.commit()

        return jsonify({"message": "User created successfully"}), 200
    else:
        return jsonify({"message": "Method Not Allowed"}), 405

@app.route('/api/updateDeadline', methods=['POST'])
def add_deadline():
    try:
        data = request.get_json()
    except Exception as e:
        return jsonify({"error": "无法解析 JSON 数据", "details": str(e)}), 400

    if 'ddl_info' not in data or 'rela_hw' not in data:
        return jsonify({"error": "缺少必要的参数"}), 400

    ddl_info = data['ddl_info']
    rela_hw_name = data['rela_hw']

    # 打印调试信息
    print(f"ddl_info = {ddl_info}")
    print(f"rela_hw_name = {rela_hw_name}")

    try:
        cursor.execute(
            "SELECT id FROM homeworks WHERE homeworkname = %s", (rela_hw_name,)
        )
        res = cursor.fetchone()
        if res is None:
            return jsonify({"error": "相关的作业未找到"}), 404

        rela_hw_id = res[0]  # 使用元组索引访问

        # 打印相关作业 ID 进行调试
        print(f"rela_hw_id = {rela_hw_id}")

        cursor.execute(
            ("UPDATE ddl SET hw_id = %s, ddl_info = %s WHERE ddl_id = 1"),(rela_hw_id, ddl_info)
        )
        conn.commit()  # 确保更改被提交到数据库

        return jsonify({"message": "截止日期添加成功"}), 201

    except Exception as e:
        # 打印完整的异常信息以便调试
        print(f"数据库操作失败: {str(e)}")
        return jsonify({"error": "数据库操作失败", "details": str(e)}), 500


@app.route('/api/deadlineList', methods=['GET'])
def get_ddl_list():
    try:
        with mysql.connector.connect(**db_config) as conn, conn.cursor() as cursor:
            # Query all DDLs
            cursor.execute("SELECT ddl_id, ddl_info, hw_id FROM ddl")
            ddl_list = cursor.fetchall()
            result = []
            for res in ddl_list:
                ddl_id = res[0]
                ddl_info = res[1]
                hw_id = res[2]

                cursor.execute("SELECT homeworkname, overtime FROM homeworks WHERE id = %s", (hw_id,))
                res2 = cursor.fetchone()
                hwname = res2[0]
                overtime = res2[1]

                result.append({
                    "id": ddl_id,
                    "info": ddl_info,
                    "overtime": overtime,
                    "hwname": hwname
                })

            return jsonify(result)
    except mysql.connector.Error as err:
        return jsonify({"error": f"Database error: {err}"}), 500
    except Exception as e:
        return jsonify({"error": str(e)}), 500

@app.route('/api/studentList', methods=['GET'])
def get_student_list():
    className = request.args.get("class")

    try:
        # 每次请求都新建连接和游标
        with mysql.connector.connect(**db_config) as conn, conn.cursor() as cursor:
            # 查询班级ID
            cursor.execute("SELECT id FROM class WHERE class_name = %s", (className,))
            res = cursor.fetchone()
            if not res:
                return jsonify({"message": "no such class found"})
            class_id = res[0]

            # 查询属于该班级的所有学生
            cursor.execute("SELECT username, user_id FROM users WHERE class_id = %s AND type = %s", (class_id, 0))
            students = cursor.fetchall()

            # 构造学生列表
            student_list = [{"name": student[0], "id": student[1]} for student in students]

            return jsonify(student_list)
    except mysql.connector.Error as err:
        # 错误处理，可以根据需要细化
        return jsonify({"error": f"Database error: {err}"}), 500


# 用户登录
@app.route("/api/login", methods=["POST"])
@route_ping
def login():
    data = request.get_json()
    username = data["username"]
    password = data["password"]
    pwd_encrypted = hash_password(password)

    cursor.execute(
        "SELECT * FROM users WHERE username=%s AND password=%s", (username, pwd_encrypted)
    )
    user = cursor.fetchone()
    if user:
        session["user_id"] = user[0]
        session["user_type"] = user[2]
        return jsonify({"message": "Login successful"}), 200
    else:
        return jsonify({"error": "Invalid username or password"}), 401


# Log Out
@app.route("/api/logout", methods=["GET"])
def logout():
    session.clear()
    return redirect("/")


@app.route("/api/get_user_name")
def get_user_name():
    try:
        return session["user_id"], 200
    except:
        return "NO LOGIN", 404


# userInfo
@app.route("/api/users/<user_id>", methods=["GET", "PUT", "DELETE"])
@route_ping
def user(user_id):
    # 查询用户信息
    if request.method == "GET":
        cursor.execute("SELECT * FROM users WHERE username=%s", (user_id,))
        user_data = cursor.fetchone()

        return jsonify({"message": "Get User successfully", "data": user_data}), 200
    # 更新用户信息
    elif request.method == "PUT":
        data = request.get_json()

        username = data["username"]
        password = data["password"]
        user_type = data["type"]

        cursor.execute(
            "UPDATE users SET username=%s, password=%s, type=%s WHERE id=%s",
            (username, password, user_type, user_id),
        )
        conn.commit()

        return jsonify({"message": "User updated successfully"}), 200
    # 删除用户信息
    elif request.method == "DELETE":
        cursor.execute("DELETE FROM users WHERE id=%s", (user_id,))
        conn.commit()

        return jsonify({"message": "User deleted successfully"}), 200
    else:
        return jsonify({"message": "Method Not Allowed"}), 405




# class
@app.route("/api/class", methods=["GET", "POST"])
@route_ping
def _class():
    if request.method == "GET":
        class_name = request.args.get("class_name")

        if class_name:
            cursor.execute("SELECT * FROM class WHERE class_name=%s", (class_name,))
            class_data = cursor.fetchone()
            return (
                jsonify({"message": "Get Class successfully", "data": class_data}),
                200,
            )
        else:
            cursor.execute("SELECT * FROM class")
            class_data = cursor.fetchall()
            return (
                jsonify(
                    {"message": "Get All Classes successfully", "data": class_data}
                ),
                200,
            )
    elif request.method == "POST":
        try:
            if session["user_type"] == 0:
                return jsonify({"message": "Request failed with status code 403"}), 403
        except:
            return jsonify({"message": "Request failed with status code 403"}), 403

        data = request.get_json()

        class_name = data["classname"]
        student = '{"student": []}'
        descriptive = data["descriptive"]

        # 检查班级名是否已经存在
        cursor.execute("SELECT * FROM class WHERE class_name = %s", (class_name,))
        existing_class = cursor.fetchone()
        if existing_class:
            return jsonify({"message": "Class name already exists"}), 400

        # 插入新班级
        cursor.execute(
            "INSERT INTO class (class_name, student, descriptive) VALUES (%s, %s, %s)",
            (class_name, student, descriptive),
        )
        conn.commit()

        return jsonify({"message": "Class created successfully"}), 200
    else:
        return jsonify({"message": "Method Not Allowed"}), 405


# course
@app.route("/api/course", methods=["GET", "POST", "DELETE", "PUT"])
@route_ping
def _course():
    if request.method == "GET":
        class_name = request.args.get("class_name")

        if class_name:
            cursor.execute("SELECT * FROM course WHERE class_name=%s", (class_name,))
            course_data = cursor.fetchall()

            return (
                jsonify({"message": "Get Courses successfully", "data": course_data}),
                200,
            )
        else:
            cursor.execute("SELECT * FROM course")
            course_data = cursor.fetchall()
            return (
                jsonify(
                    {"message": "Get All Courses successfully", "data": course_data}
                ),
                200,
            )
    elif request.method == "POST":
        try:
            if session["user_type"] == 0:
                return jsonify({"message": "Request failed with status code 403"}), 403
        except:
            return jsonify({"message": "Request failed with status code 403"}), 403

        data = request.get_json()

        course_name = data["coursename"]
        class_name = data["classname"]
        teacher = data["teacher"]
        descriptive = data["descriptive"]
        begintime = data["begintime"]
        overtime = data["overtime"]

        cursor.execute(
            "SELECT * FROM users WHERE username = %s and type = 1", (teacher,)
        )
        result = cursor.fetchone()
        if result is None:
            return jsonify({"message": "This teacher is not exists"}), 400
        # 检查课程名称是否已经存在
        cursor.execute("SELECT * FROM course WHERE course_name = %s", (course_name,))
        existing_course = cursor.fetchone()
        if existing_course:
            return jsonify({"message": "Course already exists"}), 400
        # 验证起始时间是否早于结束时间 Verify if the start time is earlier than the end time
        begin = datetime.strptime(begintime, "%Y-%m-%dT%H:%M")
        over = datetime.strptime(overtime, "%Y-%m-%dT%H:%M")
        if begin >= over:
            return jsonify({"message": "Begin time must be earlier than end time"}), 400
        # 插入新课程
        cursor.execute(
            "INSERT INTO course (course_name, class_name, teacher, descriptive, begintime, overtime, state) VALUES (%s, %s, %s, %s, %s, %s, %s)",
            (
                course_name,
                class_name,
                teacher,
                descriptive,
                begintime,
                overtime,
                "-1",
            ),
        )
        conn.commit()

        return jsonify({"message": "Course created successfully"}), 200
    elif request.method == "DELETE":
        data = request.get_json()

        course_name = data["coursename"]

        cursor.execute("DELETE FROM course WHERE course_name=%s", (course_name,))
        conn.commit()

        return jsonify({"message": "Course deleted successfully"}), 200
    elif request.method == "PUT":
        data = request.get_json()

        course_name = data["coursename"]

        # 获取当前状态
        cursor.execute(
            "SELECT state FROM course WHERE course_name = %s", (course_name,)
        )
        current_state = cursor.fetchone()
        if not current_state:
            return jsonify({"message": "Course not found"}), 404
        current_state = current_state[0]

        # 根据当前状态设置新状态
        if current_state == "-1":
            new_state = "0"
        elif current_state == "0":
            new_state = "1"
        elif current_state == "1":
            new_state = "-1"
        else:
            return jsonify({"message": "Invalid state value"}), 400

        # 更新状态
        cursor.execute(
            "UPDATE course SET state = %s WHERE course_name = %s",
            (new_state, course_name),
        )
        conn.commit()

        return (
            jsonify({"message": f"Course state updated successfully to {new_state}"}),
            200,
        )
    else:
        return jsonify({"message": "Method Not Allowed"}), 405


# homeworks
@app.route("/api/homeworks", methods=["GET", "POST", "DELETE"])
@route_ping
def _homeworks():
    if request.method == "GET":
        homeworkname = request.args.get("homeworkname")
        class_name = request.args.get("classname")

        if class_name and homeworkname:
            cursor.execute(
                "SELECT * FROM homeworks WHERE class_name=%s AND homeworkname=%s",
                (
                    class_name,
                    homeworkname,
                ),
            )
            homework_data = cursor.fetchone()

            return (
                jsonify(
                    {"message": "Get Homeworks successfully", "data": homework_data}
                ),
                200,
            )
        elif class_name:
            cursor.execute("SELECT * FROM homeworks WHERE class_name=%s", (class_name,))
            homework_data = cursor.fetchall()

            return (
                jsonify(
                    {"message": "Get Homeworks successfully", "data": homework_data}
                ),
                200,
            )
        else:
            cursor.execute("SELECT * FROM homeworks")
            homework_data = cursor.fetchall()
            return (
                jsonify(
                    {"message": "Get All Homeworks successfully", "data": homework_data}
                ),
                200,
            )
    elif request.method == "POST":
        try:
            if session["user_type"] == 0:
                return jsonify({"message": "Request failed with status code 403"}), 403
        except:
            return jsonify({"message": "Request failed with status code 403"}), 403

        data = request.get_json()

        class_name = data["classname"]
        homeworkname = data["homeworkname"]
        descriptive = data["descriptive"]
        begintime = data["begintime"]
        overtime = data["overtime"]

        # 检查作业名和班级名是否同时存在
        cursor.execute(
            "SELECT * FROM homeworks WHERE homeworkname = %s AND class_name = %s",
            (homeworkname, class_name),
        )
        existing_homework = cursor.fetchone()

        if existing_homework:
            return jsonify({"message": "Homework already exists for this class"}), 400

        # 插入新作业
        cursor.execute(
            "INSERT INTO homeworks (class_name, homeworkname, descriptive, begintime, overtime, completed) VALUES (%s, %s, %s, %s, %s, %s)",
            (
                class_name,
                homeworkname,
                descriptive,
                begintime,
                overtime,
                '{"student":[]}',
            ),
        )
        conn.commit()

        return jsonify({"message": "Homework created successfully"}), 200
    elif request.method == "DELETE":
        data = request.get_json()

        homeworkname = data["homeworkname"]
        class_name = data["classname"]

        # 检查作业名和班级名是否同时存在
        cursor.execute(
            "DELETE FROM homeworks WHERE homeworkname=%s AND class_name=%s",
            (homeworkname, class_name),
        )
        conn.commit()

        if cursor.rowcount == 0:
            return jsonify({"message": "Homework not found for this class"}), 404
        else:
            return jsonify({"message": "Homework deleted successfully"}), 200
    else:
        return jsonify({"message": "Method Not Allowed"}), 405


@app.route('/api/deleteContent/<int:content_id>', methods=['DELETE'])
def delete_content(content_id):
    try:
        cursor.execute(
            "DELETE FROM cour_content WHERE id=%s", (content_id,)
        )
        conn.commit()
        rows_affected = cursor.rowcount
        if rows_affected:
            return {"message": f"Content with ID {content_id} successfully deleted."}, 200
        else:
            return {"message": f"Failed to delete content with ID {content_id}."}, 404
    except Exception as e:
        # 记录错误或处理异常
        print(f"An error occurred: {e}")
        return {"message": "An error occurred while deleting the content."}, 500


@app.route("/api/download_file", methods=["GET"])
@route_ping
def download_file():
    file_name = request.args.get("file_name")

    if not file_name:
        return jsonify({"message": "File name is required"}), 400

    upload_folder = 'upload/files'

    try:
        return send_from_directory(upload_folder, file_name, as_attachment=True)
    except FileNotFoundError:
        return jsonify({"message": "File not found"}), 404

@app.route("/api/getContent", methods=["GET"])
def get_content():
    id = request.args.get("courseId")
    try:
        # 每次请求都新建连接和游标
        with mysql.connector.connect(**db_config) as conn, conn.cursor() as cursor:
            # 查询班级ID
            cursor.execute("SELECT * FROM cour_content WHERE course_id = %s", (id,))
            ress = cursor.fetchall()
            content_list = [{"Lec_Id": res[0], "courseInfo": res[1], "createTime": res[3], "lec_name": res[4]} for res in ress]
            return jsonify(content_list)
    except mysql.connector.Error as err:
        # 错误处理，可以根据需要细化
        return jsonify({"error": f"Database error: {err}"}), 500

@app.route("/api/publish_cour_content", methods=['POST'])
@route_ping
def publish_cour_contents():
    info = request.form.get("info")
    file = request.files.get("file")
    courseId = request.form.get("courseId")
    name = request.form.get("name")

    if file:
        upload_folder = 'upload/course'
        os.makedirs(upload_folder, exist_ok=True)
        file_path = os.path.join(upload_folder, secure_filename(file.filename))
        file.save(file_path)
    else:
        file_path = None
    current_datetime = datetime.now()
    try:
        cursor.execute(
            "INSERT INTO cour_content(info, course_id, createtime, lec_name) VALUES (%s, %s, %s, %s)",
            (info, courseId, current_datetime, name)
        )
        conn.commit()
        return jsonify({"message": "Content published successfully"}), 200
    except Exception as e:
        # 处理数据库插入失败的情况
        return jsonify({"error": "Failed to publish content", "details": str(e)}), 500

@app.route('/api/course_files_list')
def course_list_files():
    folder_path = 'upload/course'
    files = os.listdir(folder_path)
    file_list = [{'file_name': file, 'download_link': f'/api/download_file?file_name={file}'} for file in files]
    return jsonify(file_list)

@app.route('/api/list_files')
def list_files():
    folder_path = 'upload/files'
    files = os.listdir(folder_path)
    file_list = [{'file_name': file, 'download_link': f'/api/download_file?file_name={file}'} for file in files]
    return jsonify(file_list)

# submission
@app.route("/api/sub_homeworks", methods=["GET", "POST"])
@route_ping
def _sub_homeworks():
    if request.method == "GET":
        class_name = request.args.get("classname")
        homeworkname = request.args.get("homeworkname")

        if class_name and homeworkname:
            cursor.execute(
                "SELECT * FROM asr WHERE class_name=%s AND homework_name=%s",
                (class_name, homeworkname),
            )
        else:
            cursor.execute("SELECT * FROM asr")

        homework_data = cursor.fetchall()
        return (
            jsonify({"message": "Get Homeworks successfully", "data": homework_data}),
            200,
        )

    elif request.method == "POST":
        try:
            if session["user_type"] == 0:
                return jsonify({"message": "Request failed with status code 403"}), 403
        except:
            return jsonify({"message": "Request failed with status code 403"}), 403

        class_name = request.form.get("classname")
        homeworkname = request.form.get("homeworkname")
        homework_value = request.form.get("value")
        time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        student_name = request.form.get("studentname")
        file = request.files.get("file")

        if file:
            # 指定一个文件保存目录
            upload_folder = 'upload/files'
            # 确保目录存在，如果不存在则创建
            os.makedirs(upload_folder, exist_ok=True)
            # 保存文件到服务器，使用secure_filename防止文件名注入
            file_path = os.path.join(upload_folder, secure_filename(file.filename))
            file.save(file_path)
        else:
            file_path = None  # 如果没有文件上传，则设置为None或其他默认值
        # 检查作业名和班级名是否已存在
        cursor.execute(
            "SELECT * FROM asr WHERE homework_name = %s AND class_name = %s AND student_name = %s",
            (homeworkname, class_name, student_name),
        )
        existing_homework = cursor.fetchone()

        if existing_homework:
            return (
                jsonify(
                    {
                        "message": "Homework with the same name already exists for this class"
                    }
                ),
                400,
            )

        # 插入新作业
        cursor.execute(
            "INSERT INTO asr (homework_name, class_name, student_name, value, time, file, ratings, evaluation) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
            (homeworkname, class_name, student_name, homework_value, time, file_path, "", ""),
        )
        conn.commit()

        return jsonify({"message": "Homework created successfully"}), 200
    else:
        return jsonify({"message": "Method Not Allowed"}), 405


# socres
@app.route("/api/scores", methods=["POST"])
@route_ping
def _scores():
    if request.method == "POST":
        try:
            if session["user_type"] == 0:
                return jsonify({"message": "Request failed with status code 403"}), 403
        except:
            return jsonify({"message": "Request failed with status code 403"}), 403

        data = request.get_json()

        homeworkname = data.get("homework_name")
        class_name = data.get("class_name")
        student_name = data.get("student_name")
        ratings = data.get("ratings")
        evaluation = data.get("evaluation")

        if not all([homeworkname, class_name, student_name, ratings, evaluation]):
            return jsonify({"message": "Missing required fields"}), 400

        # 检查是否存在符合条件的记录
        cursor.execute(
            "SELECT * FROM asr WHERE homework_name = %s AND class_name = %s AND student_name = %s",
            (homeworkname, class_name, student_name),
        )
        existing_homework = cursor.fetchone()

        if not existing_homework:
            return jsonify({"message": "No matching record found"}), 404

        # 更新记录的 ratings 和 evaluation 字段
        cursor.execute(
            "UPDATE asr SET ratings = %s, evaluation = %s WHERE homework_name = %s AND class_name = %s AND student_name = %s",
            (ratings, evaluation, homeworkname, class_name, student_name),
        )
        conn.commit()

        return jsonify({"message": "Ratings and evaluation updated successfully"}), 200
    else:
        return jsonify({"message": "Method Not Allowed"}), 405


# ddl
@app.route("/api/get_deadline", methods=["GET"])
@route_ping
def get_deadline():
    cursor.execute(
        "SELECT ddl_info, hw_id FROM ddl"
    )
    ddl_result = cursor.fetchone()  # 由于您只需要第一条记录，使用fetchone而不是fetchall

    if ddl_result:
        ddl_info = ddl_result[0]
        hw_id = ddl_result[1]

        cursor.execute(
            "SELECT overtime FROM homeworks WHERE id = %s", (hw_id,)
        )
        overtime_result = cursor.fetchone()  # 同样，这里也使用fetchone

        if overtime_result:
            overtime = overtime_result[0]
            data = {"ddl_info": ddl_info, "overtime": overtime}
            return jsonify(data)  # 使用jsonify转换字典为JSON并返回
        else:
            return jsonify({"error": "No overtime found for the given hw_id"}), 404
    else:
        return jsonify({"error": "No ddl_info found"}), 404


# search
@app.route("/api/search", methods=["GET"])
@route_ping
def search():
    search_type = request.args.get("type")
    search_name = request.args.get("name")

    if search_type not in ["lecture", "course", "user"]:
        return jsonify({"error": "Invalid search type"}), 400

    if search_type == "course":
        cursor.execute(
            "SELECT * FROM course WHERE course_name LIKE %s", ("%" + search_name + "%",)
        )
        search_results = cursor.fetchall()
    elif search_type == "user":
        cursor.execute(
            "SELECT * FROM users WHERE username LIKE %s", ("%" + search_name + "%",)
        )
        search_results = cursor.fetchall()
    else:
        cursor.execute(
            "SELECT * FROM cour_content WHERE lec_name LIKE %s", ("%" + search_name + "%",)
        )
        search_results = cursor.fetchall()
    return jsonify({"message": "Search results", "data": search_results}), 200


# post
@app.route("/api/posts", methods=["POST"])
@route_ping  # make sure database is connecting
def create_post():
    data = request.get_json()
    course_id = data.get("course_id")
    title = data.get("title")
    content = data.get("content")
    username = session["user_id"]
    print(data)

    if not all([course_id, title, content, username]):
        return jsonify({"message": "Missing required fields"}), 400

    cursor.execute(
        "INSERT INTO posts (course_id, title, content, username, type) VALUES (%s, %s, %s, %s, %s)",
        (course_id, title, content, username, "post"),
    )
    conn.commit()

    return jsonify({"message": "Post created successfully"}), 200


# reply_post
@app.route("/api/posts/<post_id>/comments", methods=["POST"])
@route_ping
def reply_to_post(post_id):
    data = request.get_json()
    course_id = data.get("course_id")
    content = data.get("content")
    username = session["user_id"]

    if not all([content, username]):
        return jsonify({"message": "Missing required fields"}), 400

    cursor.execute(
        "INSERT INTO posts (course_id, title, parent_id, content, username, type) VALUES (%s, %s, %s, %s, %s, %s)",
        (course_id, "Comment", post_id, content, username, "comment"),
    )
    conn.commit()

    return jsonify({"message": "Comment added successfully"}), 200


# all_post
@app.route("/api/posts/<course_id>", methods=["GET"])
@route_ping
def get_posts(course_id):
    cursor.execute(
        "SELECT * FROM posts WHERE course_id = %s AND type = 'post'", (course_id,)
    )
    posts = cursor.fetchall()

    post_data = []
    for post in posts:
        cursor.execute("SELECT * FROM posts WHERE parent_id = %s", (post[0],))
        comments = cursor.fetchall()
        print(post)
        post_info = {
            "post_id": post[0],
            "title": post[3],
            "content": post[4],
            "username": post[5],
            "comments": [
                {"title": comment[3], "content": comment[4], "username": comment[5]}
                for comment in comments
            ],
        }
        post_data.append(post_info)

    return jsonify({"message": "Posts retrieved successfully", "data": post_data}), 200

@app.route("/api/get_chat_msg", methods=["GET"])
@route_ping
def get_chat_msg():
    username = request.args.get("username")
    cursor.execute("SELECT * FROM chats WHERE `from`=%s OR `to` =%s ORDER BY `time` ",(username,username))
    msg = cursor.fetchall()
    print(msg)
    return jsonify({"message": "Posts retrieved successfully", "data": msg}), 200


@app.route("/api/post_chat_msg", methods=["POST"])
@route_ping
def post_chat_msg():
    data = request.get_json()

    from_who = data["from"]
    to_who = data["to"]
    content = data["content"]
    cur_date_time = datetime.now()
    cursor.execute("INSERT INTO chats (`from`, `to`, `content`, `time`) VALUES (%s, %s, %s,%s)",(from_who,to_who, content, cur_date_time))
    conn.commit()
    return jsonify({"message":"Msg sent successfully."}),200

@app.errorhandler(jinja2.exceptions.TemplateNotFound)
def handle_template_not_found(e):
    return


if __name__ == "__main__":
    app.run(port=1024)
